﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DBUtility
{
    public class DBControl
    {
        /// <summary>
        /// SQL语句（增删改），并返回行数
        /// </summary>
        /// <param name="_SQLScript"></param>
        /// <returns></returns>
        public int ExecuteSqlCommand(string _SQLScript)
        {
            int _Result = -1;
            using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
            {
                _Result = _LMPAlarmEntities.Database.ExecuteSqlCommand(_SQLScript);

                _LMPAlarmEntities.SaveChanges();
            }

            return _Result;
        }

        /// <summary>
        /// SQL语句--Database.SqlQuery
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_SQLScript"></param>
        /// <returns></returns>
        public List<T> SqlQuery1<T>(string _SQLScript) where T : class
        {
            List<T> _Result;
            using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
            {
                _Result = _LMPAlarmEntities.Database.SqlQuery<T>(_SQLScript).ToList();
            }
            return _Result;
        }

        /// <summary>
        /// SQL语句--Set<T>().SqlQuery
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_SQLScript"></param>
        /// <returns></returns>
        public List<T> SqlQuery2<T>(string _SQLScript) where T : class
        {
            List<T> _Result;
            using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
            {
                _Result = _LMPAlarmEntities.Set<T>().SqlQuery(_SQLScript).ToList();

                _LMPAlarmEntities.SaveChanges();
            }
            return _Result;
        }

        /// <summary>
        /// 查询表中某些列
        /// </summary>
        /// <typeparam name="T1">表</typeparam>
        /// <typeparam name="T2">部分列组成类型</typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        public List<T2> SelectSomeColumn<T1, T2>(Func<T1, T2> func) where T1 : class
        {
            List<T2> _Result = null;
            try
            {
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    _Result = _LMPAlarmEntities.Set<T1>().Select(func).ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return _Result;
        }

        /// <summary>
        /// 查询--全部
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> SelectAll<T>() where T : class
        {
            List<T> _Result = null;
            try
            {
                //LMPAlarmEntities
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    //_LMPAlarmEntities.Configuration.LazyLoadingEnabled = true;
                    _Result = _LMPAlarmEntities.Set<T>().ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return _Result;
        }

        /// <summary>
        /// 查询--Where
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        public List<T> SelectAll<T>(Func<T, bool> func) where T : class
        {
            List<T> _Result = null;
            try
            {
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    _Result = _LMPAlarmEntities.Set<T>().Where(func).ToList();
                }
            }
            catch (Exception)
            {

            }
            return _Result;
        }

        /// <summary>
        /// 查询--单个
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        public T SelectSingle<T>(Func<T, bool> func) where T : class
        {
            T _Result = null;
            try
            {
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    _Result = _LMPAlarmEntities.Set<T>().FirstOrDefault(func);
                }
            }
            catch (Exception ex)
            {
            }
            return _Result;
        }

        /// <summary>
        /// 添加数据--AddRange
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void Insert<T>(List<T> list) where T : class
        {
            using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
            {
                var tran = _LMPAlarmEntities.Database.BeginTransaction();
                try
                {
                    _LMPAlarmEntities.Set<T>().AddRange(list);

                    _LMPAlarmEntities.SaveChanges();
                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                }
            }
        }

        /// <summary>
        /// 添加数据--EntryState
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void Insert2<T>(List<T> list) where T : class
        {
            using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
            {
                var tran = _LMPAlarmEntities.Database.BeginTransaction();
                try
                {
                    foreach (var item in list)
                    {
                        _LMPAlarmEntities.Entry<T>(item).State = EntityState.Added;
                    }
                    _LMPAlarmEntities.SaveChanges();

                    tran.Commit();

                }
                catch (Exception)
                {
                    tran.Rollback();
                }
            }
        }

        /// <summary>
        /// 利用对象删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Remove<T>(T t) where T : class
        {
            try
            {
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    //方法1
                    _LMPAlarmEntities.Set<T>().Attach(t);
                    _LMPAlarmEntities.Set<T>().Remove(t);
                    //方法2
                    //_LMPAlarmEntities.Entry<T>(t).State = System.Data.Entity.EntityState.Deleted;
                    _LMPAlarmEntities.SaveChanges();
                }
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <returns></returns>
        public int Update<T>(T t) where T : class
        {
            try
            {
                using (LMPAlarmEntities context = new LMPAlarmEntities())
                {
                    context.Entry<T>(t).State = EntityState.Modified;
                    return context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
        }


        //测试方法

        
        public IEnumerable<T> Select<T>(Func<T, bool> selector, string[] includes) where T : class
        {
            IEnumerable<T> result = null;
            try
            {
                using (LMPAlarmEntities context = new LMPAlarmEntities())
                {
                    IQueryable<T> query = context.Set<T>().Where(x => true);
                    if (includes != null && includes.Count() > 0)
                    {
                        foreach (string path in includes)
                        {
                            query = query.Include(path);
                        }
                    }
                    result = query.Where(selector).ToList();
                }
            }
            catch (Exception ex)
            {
                //PrintExceptionLog(ex);
            }
            return result;
        }
        public List<T> SelectAll111<T>(string include) where T : class
        {
            List<T> _Result = null;
            try
            {
                //LMPAlarmEntities
                using (LMPAlarmEntities _LMPAlarmEntities = new LMPAlarmEntities())
                {
                    //_LMPAlarmEntities.Configuration.LazyLoadingEnabled = true;
                    _Result = _LMPAlarmEntities.Set<T>().Include(include).ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return _Result;
        }

        ///// <summary>
        ///// Select Single Join Tables
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="selector"></param>
        ///// <param name="includePaths"></param>
        ///// <returns></returns>
        //public T SelectSingle<T>(Func<T, bool> selector, string[] includePaths) where T : class
        //{
        //    T result = null;
        //    try
        //    {
        //        using (DataAccessorObjectEntities context = new DataAccessorObjectEntities())
        //        {
        //            ObjectQuery<T> query = context.CreateObjectSet<T>().Where("1==1");
        //            if (includePaths != null && includePaths.Count() > 0)
        //            {
        //                foreach (string path in includePaths)
        //                {
        //                    query = query.Include(path);
        //                }
        //            }
        //            result = query.FirstOrDefault(selector);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        PrintExceptionLog(ex);
        //    }
        //    return result;
        //}

        /// <summary>
        /// Select Collection Join Tables
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="selector"></param>
        /// <param name="includes"></param>
        /// <returns></returns>
        public IEnumerable<T> Select123<T>(Func<T, bool> selector, string[] includes) where T : class
        {
            IEnumerable<T> result = null;
            try
            {
                using (LMPAlarmEntities context = new LMPAlarmEntities())
                {
                    IQueryable<T> query = context.Set<T>().Where(x=> true);
                    if (includes != null && includes.Count() > 0)
                    {
                        foreach (string path in includes)
                        {
                            query = query.Include(path);
                        }
                    }
                    result = query.Where(selector).ToList();
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }
        

    }
}
